package app;

/********************************************************
 *
 *  Project :  TeamProject
 *  Package :  app
 *  File    :  Cookbook.java
 *  Name    :  Tyler Daines
 *  Date    :  Nov 3, 2013
 *
 ********************************************************/


import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;

import javax.swing.JComboBox;

public class Cookbook
{

	private Connection connection = null;
	private Statement statement = null;
	private ResultSet resultSet = null;
	private ResultSet resultSet2 = null;
	private HashMap<Integer, String> categoryMap;

	public Cookbook()
	{
		categoryMap = getCategoryHashMap();
	}
	public int addRecipeToDB(Recipe recipe)
	{

		connectToDB();
		int re_ID = -1;
		try
		{
			// set up recipe info to be added
			String name = recipe.getName();
			name = name.replaceAll("'", "''");
			String directions = recipe.getDirections();
			directions = directions.replaceAll("'", "''");
			int categoryID = recipe.getCategoryID();
			boolean favorite = recipe.isFavorite();
			float servingSize = recipe.getServingSize();
			float calories = recipe.getCalories();
			String picturePath = recipe.getPicture();
			picturePath = picturePath.replaceAll("'", "''");

			// generate sql using above recipe info
			String sql = "INSERT INTO tbl_Recipe (re_Name,re_Directions,re_CategoryID,re_Favorite,re_ServingSize,re_Calories,re_Picture) "
					+ "VALUES ('"
					+ name
					+ "', '"
					+ directions
					+ "',"
					+ categoryID
					+ ", '"
					+ favorite
					+ "', "
					+ servingSize
					+ ", " + calories + ", '" + picturePath + "' );";
			// send sql command to db
			statement.executeUpdate(sql);

			// get just added recipe's unique ID from the db (need this to add
			// ingredients to tbl_Ingredient
			resultSet = statement
					.executeQuery("SELECT * FROM tbl_Recipe where re_ID= (select max(re_ID) from tbl_Recipe);");
			re_ID = resultSet.getInt("re_ID");
			recipe.setRe_uniqueID(re_ID);

			// get list of ingredients from recipe
			ArrayList<Ingredient> ingredients = recipe.getIngredients();
			if (ingredients.isEmpty() == false)
			{
				// for each ingredient get info and insert into db
				for (Ingredient ing : ingredients)
				{
					float in_amount = ing.getAmount();
					String in_name = ing.getName();
					in_name = in_name.replaceAll("'", "''");
					// int in_measureID = ing.getMeasurement().ordinal();
					// //11/14/13 cmh change to String
					String in_measureID = ing.getMeasurement().toString();
					sql = "INSERT INTO tbl_Ingredient (in_RecipeID,in_Amount,in_Name, in_MeasurementID) "
							+ "VALUES ("
							+ re_ID
							+ ", "
							+ in_amount
							+ ", '"
							+ in_name + "', '" + in_measureID + "' );";
					statement.executeUpdate(sql);
				}
			}
		}
		catch (Exception exception)
		{
			System.err.println(exception.getClass().getName());
			exception.printStackTrace();
			System.exit(0);
		}
		System.out.println("Record created successfully");
		disconnectFromDB();
		return re_ID;
	}// end addRecipeToDB

	public void updateRecipeInDB(Recipe recipe)
	{
		connectToDB();
		try
		{
			// set up recipe info to be updated
			int re_uniqueID = recipe.getRe_uniqueID();
			String name = recipe.getName();
			name = name.replaceAll("'", "''");
			String directions = recipe.getDirections();
			directions = directions.replaceAll("'", "''");
			int categoryID = recipe.getCategoryID();
			boolean favorite = recipe.isFavorite();
			float servingSize = recipe.getServingSize();
			float calories = recipe.getCalories();
			String picturePath = recipe.getPicture();
			picturePath = picturePath.replaceAll("'", "''");
			// generate sql using above recipe info
			String sql = "UPDATE tbl_Recipe set re_Name='" + name
					+ "',re_Directions='" + directions + "',re_CategoryID="
					+ categoryID + ",re_Favorite='" + favorite
					+ "',re_ServingSize=" + servingSize + ",re_Calories="
					+ calories + ",re_Picture='" + picturePath
					+ "' where re_ID=" + re_uniqueID + ";";
			// send sql command to db
			statement.executeUpdate(sql);

			// update ingredients
			ArrayList<Ingredient> ingredients = recipe.getIngredients();
			for (int i = 0; i < ingredients.size(); i++)
			{
				Integer in_uniqueID = ingredients.get(i).getIn_uniqueID();
				float new_in_amount = ingredients.get(i).getAmount();
				String new_in_name = ingredients.get(i).getName();
				new_in_name = new_in_name.replaceAll("'", "''");
				// int new_in_measure =
				// ingredients.get(i).getMeasurement().ordinal(); //11.14.13 cmh
				// change type to String
				String new_in_measure = ingredients.get(i).getMeasurement()
						.toString();
				// if the in_uniqueID is -1 it is a newly added ingredient
				if (in_uniqueID.equals(-1) == false)
				{
					sql = "UPDATE tbl_Ingredient set in_Amount="
							+ new_in_amount + ",in_Name='" + new_in_name
							+ "',in_MeasurementID='" + new_in_measure
							+ "' where in_ID=" + in_uniqueID + ";";
					statement.executeUpdate(sql);
					// otherwise it will be update based on the in_uniqueID
				}
				else if (in_uniqueID.equals(-1) == true)
				{
					sql = "INSERT INTO tbl_Ingredient (in_RecipeID,in_Amount,in_Name,in_MeasurementID) "
							+ "VALUES ("
							+ re_uniqueID
							+ ", "
							+ new_in_amount
							+ ", '"
							+ new_in_name
							+ "', '"
							+ new_in_measure
							+ "' );";
					statement.executeUpdate(sql);
					// Immediately need to update the current in_uniqueID's for
					// the ingredients in this recipes ArrayList<Ingredient>
					// This is in order to properly remove deleted ingredients
					// from tbl_ingredient
					resultSet = statement
							.executeQuery("SELECT * FROM tbl_Ingredient where in_ID= (select max(in_ID) from tbl_Ingredient);");
					int in_ID = resultSet.getInt("in_ID");
					ingredients.get(i).setIn_uniqueID(in_ID);
				}
			}
			// generate list of all existing in_ID based on the current
			// in_RecipeID
			ArrayList<Integer> in_idArray = new ArrayList<Integer>();
			resultSet = statement
					.executeQuery("SELECT in_ID FROM tbl_Ingredient where in_RecipeID="
							+ re_uniqueID + ";");
			int in_ID = 0;
			while (resultSet.next())
			{
				in_ID = resultSet.getInt("in_ID");
				in_idArray.add(in_ID);
			}
			// check if each in_ID from the db is in the current recipes
			// ingredient ArrayList, if found mark boolean delete false
			for (Integer in_ids : in_idArray)
			{
				boolean delete = true;
				for (Ingredient ings : ingredients)
				{
					if ((in_ids.equals(ings.getIn_uniqueID()) == true))
					{
						delete = false;
					}
				}
				// if the in_ID was not found in the current ingredient list
				// then boolean delete should be true,
				// which should then deleted that row from the db
				if (delete)
				{
					sql = "DELETE from tbl_Ingredient where in_ID=" + in_ids
							+ ";";
					statement.executeUpdate(sql);
				}
			}
			disconnectFromDB();
		}
		catch (Exception exception)
		{
			System.err.println(exception.getClass().getName());
			exception.printStackTrace();
			System.exit(0);
		}
		System.out.println("Record updated successfully");

	}// end updateRecipeInDB

	public void searchRecipe(Recipe recipe)
	{
		connectToDB();
		try
		{
			resultSet = statement.executeQuery("SELECT * FROM RECIPE;");
			while (resultSet.next())
			{
				int re_uniqueID = recipe.getRe_uniqueID();
				String name = recipe.getName();
				String directions = recipe.getDirections();
				int categoryID = recipe.getCategoryID();
				boolean favorite = recipe.isFavorite();
				float servingSize = recipe.getServingSize();
				float calories = recipe.getCalories();
				String picturePath = recipe.getPicture().toString();

				String rep = "Recipe: '" + name + "',Direction: '" + directions
						+ "',Category: " + categoryID + ",Favorite: '"
						+ favorite + "';Serving size: " + servingSize
						+ ",Calories: " + calories + ",Picture: '"
						+ picturePath + "'Recipe ID # " + re_uniqueID + ";";
				System.out.println(rep);
			}
			disconnectFromDB();
		}
		catch (Exception e)
		{
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			e.printStackTrace();
			System.exit(0);
		}
		System.out.println("Search found");

	}

	// Adds categories to a combobox
	public void pullCategories(JComboBox<String> comboBox)
	{
		try
		{
			String combo = "SELECT ca_Name from tbl_Category";
			resultSet = statement.executeQuery(combo);

			while (resultSet.next())
			{
				String category = resultSet.getString("ca_Name");
				comboBox.addItem(category);
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
			System.err.println(e);
		}
	}

	public LinkedHashMap<Integer, String> getCategoryHashMap()
	{
		connectToDB();
		LinkedHashMap<Integer, String> mapResult = new LinkedHashMap<Integer, String>();
		try
		{
			String combo = "SELECT * from tbl_Category";
			resultSet = statement.executeQuery(combo);
			mapResult.put(0, "Select a Category");
			while (resultSet.next())
			{
				Integer id = resultSet.getInt("ca_ID");
				String category = resultSet.getString("ca_Name");
				mapResult.put(id, category);
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
			System.err.println(e);
		}
		disconnectFromDB();
		return mapResult;
	}

	// Adds categories to a combobox
	public String pullCategory(int categoryId)
	{
		if (categoryId != 0)
		{
			return categoryMap.get(categoryId);
			/*
			 * connectToDB(); try { String category =
			 * "SELECT ca_Name from tbl_Category WHERE ca_ID="+categoryId+";";
			 * resultSet = statement.executeQuery(category); String categoryName
			 * = resultSet.getString("ca_Name"); disconnectFromDB(); return
			 * categoryName;
			 * 
			 * } catch (Exception e) { e.printStackTrace();
			 * System.err.println(e); }
			 */
		}
		return "";
	}

	/**
	 * Method: deleteRecipeInDB Purpose: to delete a Recipe record and
	 * Ingredients in the database
	 * 
	 * @param int recipeID - the ID of the recipe record that will be deleted
	 * @author Claudine Haight
	 */
	public void deleteRecipeInDB(int recipeID)
	{
		connectToDB();

		try
		{
			// delete ingredients
			String sql = "DELETE FROM tbl_Ingredient "
					+ "WHERE ([in_RecipeID]=" + recipeID + ");";
			statement.executeUpdate(sql);
			// delete recipe
			sql = "DELETE FROM tbl_Recipe " + "WHERE ([re_ID]=" + recipeID
					+ ");";
			statement.executeUpdate(sql);
			// commit and close
			disconnectFromDB();
		}
		catch (Exception exception)
		{
			System.err.println(exception.getClass().getName());
			exception.printStackTrace();
			System.exit(0);
		}

	}// end deleteRecipeInDB

	public ArrayList<Recipe> viewAllRecipes()
	{
		ArrayList<Recipe> theList = new ArrayList<Recipe>();
		connectToDB();

		try
		{
			resultSet = statement.executeQuery("SELECT * FROM tbl_Recipe;");
			ArrayList<Integer> idArray = new ArrayList<Integer>();
			while (resultSet.next())
			{
				idArray.add(resultSet.getInt("re_ID"));
			}
			resultSet = null;
			for (Integer ids : idArray)
			{
				Recipe re = createRecipeFromDBHaveConnection(ids);
				theList.add(re);
			}
		}
		catch (Exception exception)
		{
			System.err.println(exception.getClass().getName());
			exception.printStackTrace();
			System.exit(0);
		}
		disconnectFromDB();
		return sortArrayList(theList);
	}

	/**
	 * Method to sort the arrayList by category->name->calories->serving size.
	 * compareTo implemented in the Recipe class.
	 */

	public ArrayList<Recipe> sortArrayList(ArrayList<Recipe> list)
	{	
		Collections.sort(list);
		return list;
	}

	/**
	 * Method: searchRecipeByIngredient Purpose: to search the database for the
	 * ingredient and category (if not 0)
	 * 
	 * @return a Recipe array for all recipe records that use that ingredient
	 * @param String
	 *            ingredient: the ingredient name that is being used to filter
	 *            the result set
	 * @throws SQLException
	 */
	@SuppressWarnings("null")
	public List<Recipe> searchRecipeByIngredient(String ingredient, int category)
			throws SQLException
	{
		System.out.printf(ingredient, category);
		System.out.println();

		List<Recipe> recipes = new ArrayList<Recipe>();
		List<Integer> re_ids = new ArrayList<Integer>();
		connectToDB();
		try
		{
			statement = connection.createStatement();
			ingredient = ingredient.replaceAll("'", "''");
			String sql;
			// search for ingredient
			sql = "SELECT DISTINCT in_RecipeID " + "FROM tbl_Ingredient "
					+ "WHERE (in_Name like '%" + ingredient + "%') "
					+ "ORDER BY in_RecipeID;";

			// create recordset
			resultSet = statement.executeQuery(sql);
			while (resultSet.next())
			{
				re_ids.add(resultSet.getInt("in_RecipeID"));
			}
			// add only recipes with correct category to list
			if (category != 0)
			{
				for (Integer id : re_ids)
				{
					Recipe tempRecipe = createRecipeFromDBHaveConnection(id);
					if (tempRecipe.getCategoryID().equals(category))
					{
						recipes.add(tempRecipe);
					}
				}
			}
			// else add all recipes to the list
			else
			{
				for (Integer id : re_ids)
				{
					recipes.add(createRecipeFromDBHaveConnection(id));
				}
			}
		}
		catch (SQLException | ClassNotFoundException e)
		{
			e.printStackTrace();
		}
		disconnectFromDB();
		return recipes;
	}

	/**
	 * Method: connectToDB Purpose: to open a connection to the database
	 * 
	 * @author Claudine
	 * @return Connection
	 */
	private void connectToDB()
	{
		connection = null;
		statement = null;
		resultSet = null;
		resultSet2 = null;
		try
		{
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager
					.getConnection("jdbc:sqlite:RecipeData.s3db");
			System.out.println("Opened database successfully");
			statement = connection.createStatement();

		}
		catch (Exception e)
		{
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			e.printStackTrace();
			System.exit(0);
		}

	}// end connectToDB method

	private void disconnectFromDB()
	{
		try
		{
			if ((resultSet == null) == false)
			{
				resultSet.close();
			}
			if ((resultSet2 == null) == false)
			{
				resultSet2.close();
			}
			if ((statement == null) == false)
			{
				statement.close();
			}
			if ((connection == null) == false)
			{
				connection.close();
			}
		}
		catch (Exception e)
		{
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			e.printStackTrace();
			System.exit(0);
		}
		connection = null;
		statement = null;
		resultSet = null;
		resultSet2 = null;
		System.out.println("Disconnected from database successfully");
	}

	/**
	 * Method: createRecipeFromDB Purpose: to create a Recipe object from the
	 * recipe record in the database
	 * 
	 * @param ID
	 *            : the re_ID of the recipe record in the db
	 * @return Recipe object
	 * @throws ClassNotFoundException
	 */
	public Recipe createRecipeFromDB(int ID) throws ClassNotFoundException
	{
		Recipe recipe = null;
		connectToDB();
		recipe = createRecipeFromDBHaveConnection(ID);
		disconnectFromDB();
		return recipe;
	}

	private Recipe createRecipeFromDBHaveConnection(int ID)
			throws ClassNotFoundException
	{
		Recipe recipe = null;
		ArrayList<Ingredient> ingredients = new ArrayList<Ingredient>();
		// get recipe from DB
		try
		{
			String sql = "SELECT * " + "FROM tbl_Recipe " + "WHERE ([re_ID]="
					+ ID + ");";
			System.out.println(sql);
			resultSet = statement.executeQuery(sql);
			while (resultSet.next())
			{
				// add recipe to the array list
				recipe = new Recipe(
						resultSet.getInt("re_ID"),
						resultSet.getString("re_Name"),
						null,
						resultSet.getString("re_Directions"),
						resultSet.getFloat("re_Calories"),
						resultSet.getFloat("re_ServingSize"),
						resultSet.getString("re_Picture"),
						Boolean.parseBoolean(resultSet.getString("re_Favorite")),
						resultSet.getInt("re_CategoryID"),
						this.pullCategory(resultSet.getInt("re_CategoryID")));
			}
			// create an array of ingredients for recipe
			resultSet = statement.executeQuery("SELECT * "
					+ "FROM tbl_Ingredient " + "WHERE (in_RecipeID = " + ID
					+ ");");
			while (resultSet.next())
			{
				ingredients.add(new Ingredient(resultSet.getInt("in_ID"),
						resultSet.getString("in_Name"), resultSet
								.getFloat("in_Amount"),
						findMeasurementByAbbrv(resultSet
								.getString("in_MeasurementID"))));
			}
			recipe.setIngredients(ingredients);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return recipe;
	}// end createRecipeFromDB method

	public ArrayList<Recipe> searchByCategory(int ca_ID)
	{
		ArrayList<Recipe> recipeByCateogry = new ArrayList<>();
		ArrayList<Integer> recipeIDArray = new ArrayList<>();
		connectToDB();

		try
		{
			resultSet = statement
					.executeQuery("SELECT * FROM tbl_Recipe WHERE re_CategoryID="
							+ ca_ID + ";");

			while (resultSet.next())
			{
				int recipeID = resultSet.getInt("re_ID");
				recipeIDArray.add(recipeID);
			}

			for (Integer r : recipeIDArray)
			{
				Recipe match = createRecipeFromDB(r);
				recipeByCateogry.add(match);
			}

		}
		catch (Exception e)
		{
			System.err.println(e.getClass().getName());
			e.printStackTrace();
			System.exit(0);
		}

		disconnectFromDB();

		return recipeByCateogry;

	}

	// Search all favorite recipe method
	public ArrayList<Recipe> searchByFavorite()
	{
		ArrayList<Recipe> recipeByFavorite = new ArrayList<>();
		ArrayList<Integer> recipeIDArray = new ArrayList<>();
		connectToDB();

		try
		{
			resultSet = statement
					.executeQuery("SELECT * FROM tbl_Recipe WHERE re_Favorite='true';)");

			while (resultSet.next())
			{
				int recipeID = resultSet.getInt("re_ID");
				recipeIDArray.add(recipeID);
			}

			for (Integer r : recipeIDArray)
			{
				Recipe match = createRecipeFromDBHaveConnection(r);
				recipeByFavorite.add(match);
			}

		}
		catch (Exception e)
		{
			System.err.println(e.getClass().getName());
			e.printStackTrace();
			System.exit(0);
		}

		disconnectFromDB();

		return sortArrayList(recipeByFavorite);

	}// end searchByFavorite

	// Search recipe by name
	public ArrayList<Recipe> searchByName(String name)
	{
		ArrayList<Recipe> recipeByCateogry = new ArrayList<>();
		ArrayList<Integer> recipeIDArray = new ArrayList<>();
		connectToDB();
		name = name.replaceAll("'", "''");
		try
		{
			resultSet = statement
					.executeQuery("SELECT DISTINCT * FROM tbl_Recipe "
							+ "WHERE (re_Name like '%" + name + "%') "
							+ "ORDER BY re_Name;");
			while (resultSet.next())
			{
				int recipeID = resultSet.getInt("re_ID");
				recipeIDArray.add(recipeID);
			}

			for (Integer r : recipeIDArray)
			{
				Recipe match = createRecipeFromDB(r);
				recipeByCateogry.add(match);
			}

		}
		catch (Exception e)
		{
			System.err.println(e.getClass().getName());
			e.printStackTrace();
			System.exit(0);
		}

		disconnectFromDB();

		return recipeByCateogry;

	}// end searchByName

	/**
	 * Method: FindMeasurementByAbbrv Purpose: to find and return the
	 * Measurement for the abbreviation passed in
	 * 
	 * @param abbrv
	 * @return Measurement that matches the abbreviation
	 */
	public Measurement findMeasurementByAbbrv(String abbrv)
	{
		Measurement measurement = null;

		for (Measurement m : Measurement.values())
		{
			if (m.getAbbreviation().equals(abbrv))
				measurement = m;
		}

		return measurement;
	}

	public void saveRecipeToTxtFile(Recipe recipe, File file)
	{
		BufferedWriter write = null;
		String filePath = file.toString();
		if (!filePath.endsWith(".txt"))
			filePath += ".txt";
		try
		{
			write = new BufferedWriter(new FileWriter(filePath));
			write.newLine();
			write.write(recipe.getName());
			write.newLine();
			write.newLine();
			write.write("Ingredients:");
			write.newLine();
			for (Ingredient ing : recipe.getIngredients())
			{
				String in_Str = String.format("%5.2f%8s%20s", ing.getAmount(),
						ing.getMeasurement(), ing.getName());
				write.write(in_Str);
				write.newLine();
			}
			write.newLine();
			write.write("Directions:");
			write.newLine();
			String[] directions = recipe.getDirections().split("\\s");
			int counter = 0;
			for (String d : directions)
			{
				if (counter < 50)
				{
					write.write(d);
					counter += d.length();
				}
				else
				{
					write.newLine();
					counter = 0;
				}
			}
			write.newLine();
			write.newLine();
			write.write("Calories:");
			write.newLine();
			write.write(Float.toString(recipe.getCalories()));
			write.newLine();
			write.newLine();
			write.write("Servings:");
			write.newLine();
			write.write(Float.toString(recipe.getServingSize()));
			write.newLine();
			write.newLine();

		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if (write != null)
				{
					write.close();
				}
			}
			catch (Exception e)
			{
				e.printStackTrace();
			}
		}
	}

	/**
	 * Method: addMealToDB Purpose: to add new meal bundle records to the
	 * database
	 * 
	 * @return int = the ID number of the new meal record
	 */
	public int addMealToDB(MealBundle meal)
	{
		int ID = 0;

		try
		{
			// get an ID
			ID = getNextMealID();
			connectToDB();
			for (Recipe recipe : meal.getRecipes())
			{
				String mealName = meal.getName();
				mealName = mealName.replaceAll("'", "''");
				// generate sql to add meal bundle records
				String sql = "INSERT INTO tbl_RecipeBundle (rb_ID, rb_RecipeID, rb_Name) "
						+ "VALUES ("
						+ ID
						+ ", "
						+ recipe.getRe_uniqueID()
						+ ", '" + mealName + "');";
				// send sql command to db
				statement.executeUpdate(sql);
			}
		}
		catch (Exception exception)
		{
			System.err.println(exception.getClass().getName());
			exception.printStackTrace();
			System.exit(0);
		}
		System.out.println("Record created successfully");
		disconnectFromDB();

		return ID;
	}

	/**
	 * Method: updateMealInDB Purpose: to update existing meal bundle records in
	 * the database
	 */
	public void updateMealInDB(MealBundle meal)
	{
		int ID;
		int recCount = 0;
		connectToDB();
		try
		{
			// get an ID
			ID = meal.getID();
			String sql;
			for (Recipe recipe : meal.getRecipes())
			{
				recCount = 0;
				String sql1 = "SELECT Count(rb_ID) AS cnt FROM tbl_RecipeBundle "
						+ "WHERE rb_ID="
						+ ID
						+ " AND rb_RecipeID="
						+ recipe.getRe_uniqueID() + ";";
				resultSet = statement.executeQuery(sql1);
				while (resultSet.next())
					recCount = resultSet.getInt("cnt");

				if (recCount > 0)
				{
					String mealName = meal.getName();
					mealName = mealName.replaceAll("'", "''");
					// generate sql to add meal bundle records
					sql = "UPDATE tbl_RecipeBundle  SET " + "rb_RecipeID = "
							+ recipe.getRe_uniqueID() + ", rb_Name = '"
							+ mealName + "' " + "WHERE rb_ID=" + ID
							+ " AND rb_RecipeID=" + recipe.getRe_uniqueID()
							+ ";";
				}
				else
				{
					String mealName = meal.getName();
					mealName = mealName.replaceAll("'", "''");
					sql = "INSERT INTO tbl_RecipeBundle (rb_ID, rb_RecipeID, rb_Name) "
							+ "VALUES ("
							+ ID
							+ ", "
							+ recipe.getRe_uniqueID()
							+ ", '" + mealName + "');";
				}
				// send sql command to db
				statement.executeUpdate(sql);
			}
		}
		catch (Exception exception)
		{
			System.err.println(exception.getClass().getName());
			exception.printStackTrace();
			System.exit(0);
		}
		System.out.println("Record updated successfully");
		disconnectFromDB();
	}

	/**
	 * Method: deleteMealInDB Purpose: to delete the meal record(s)
	 * 
	 * @param int mealID - the ID of the meal records that will be deleted
	 */
	public void deleteMealInDB(int mealID)
	{
		connectToDB();

		try
		{
			// delete all meal records
			String sql = "DELETE FROM tbl_RecipeBundle " + "WHERE ([rb_ID]="
					+ mealID + ");";
			statement.executeUpdate(sql);
		}
		catch (Exception exception)
		{
			System.err.println(exception.getClass().getName());
			exception.printStackTrace();
			System.exit(0);
		}
		disconnectFromDB();

	}// end deleteRecipeInDB

	/**
	 * Method: getNextMealID Purpose: to get the next sequential number for the
	 * meal ID
	 */
	private int getNextMealID()
	{
		int ID = 0;
		connectToDB();
		try
		{
			String sql = "SELECT Max(rb_ID) AS MaxID "
					+ "FROM tbl_RecipeBundle;";
			resultSet = statement.executeQuery(sql);
			while (resultSet.next())
			{
				ID = resultSet.getInt("MaxID");
			}
		}
		catch (Exception exception)
		{
			System.err.println(exception.getClass().getName());
			exception.printStackTrace();
			System.exit(0);
		}
		disconnectFromDB();
		ID++;
		return ID;
	}// end method getNextMealID

	/**
	 * Method: getRecipesInMeal Purpose: get all of the Recipes in a meal bundle
	 * 
	 * @param int mealID: meal bundle ID
	 * @return ArrayList<Recipe>
	 */
	public ArrayList<Recipe> getRecipesInMeal(int mealID)
	{
		ArrayList<Recipe> mealRecipes = new ArrayList<Recipe>();
		connectToDB();
		try
		{
			String sql = "SELECT rb_RecipeID " + "FROM tbl_RecipeBundle "
					+ "WHERE rb_ID =" + mealID + ";";
			resultSet = statement.executeQuery(sql);
			ArrayList<Integer> recipeIDs = new ArrayList<Integer>();
			while (resultSet.next())
			{
				recipeIDs.add(resultSet.getInt("rb_RecipeID"));
			}
			disconnectFromDB();
			for (Integer id : recipeIDs)
			{
				mealRecipes.add(createRecipeFromDB(id));
			}
		}
		catch (Exception exception)
		{
			System.err.println(exception.getClass().getName());
			exception.printStackTrace();
			disconnectFromDB();
			System.exit(0);
		}

		return mealRecipes;
	}// end method getRecipesInMeal

	/**
	 * Method: getMealHashMap Purpose: pulls dataset of all Meal Bundles in the
	 * database
	 * 
	 * @return LinkedHashMap<Integer, String> Integer = mealID, String = name
	 */
	public LinkedHashMap<Integer, String> getMealHashMap()
	{
		connectToDB();
		LinkedHashMap<Integer, String> mapResult = new LinkedHashMap<Integer, String>();
		try
		{
			String combo = "SELECT DISTINCT rb_ID, rb_Name FROM tbl_RecipeBundle ORDER BY rb_Name;";
			resultSet = statement.executeQuery(combo);
			mapResult.put(0, "Select a Meal");
			while (resultSet.next())
			{
				Integer id = resultSet.getInt("rb_ID");
				String name = resultSet.getString("rb_Name");
				mapResult.put(id, name);
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
			System.err.println(e);
		}
		disconnectFromDB();
		return mapResult;
	}// end method getRecipesInMeal

}
